Time Calculation Wizard

The Time Calculation Wizard in Discover is designed to help users quickly and easily generate a variety of date-time calculations for your analysis. The resulting calculations are generated as custom elements (lists and formulas) which can be easily incorporated within your reports. The wizard orchestrates the different steps needed for each calculation on a given target date-time hierarchy, rather than having the user manually build out the various calculation elements using the Formulate tools.

The simple point-and-click interface allows users select whichever calculations they want to generate given the type of date-time hierarchy presented. The generated calculations then appear the hierarchy's custom element trees where they can added to the query, saved and shared, and more like any other custom calculation.

Note: this feature is not available in the Community edition.

Using the Time Calculation Wizard

The Time Calculation wizard dialog is available from the Query ribbon, in the 'functions' group.

Clicking the button will allow you to add new date and time calculations to the selected date-time hierarchy. The wizard will open from the bottom panel section in Discover.

Calculation Meta Settings

The meta settings provide the information for selecting the target hierarchy and how to save the generated calculations.

• Hierarchies: The first step is to select a target hierarchy that will be used for generating relevant calculations and where the resulting logic will be housed (red arrow above). Only hierarchies that have been assigned a date-time type will appear as available options.
  • Date hierarchies can be assigned a date type either by assigning the relevant category in the data modeling process, or an overlay in Discover or the Admin console. The date types supported are Date, Week Number, Month, Quarter, and Year.
  • Date-time categorization can also be read from models built in MS OLAP or SAP BW,
• Caption: This provide a group name for the all the calculation items that will be generated
• Parameterize All Calculations: This will auto-create and then auto-attach a parameter to all calculations. Parameterization will allow the user to tweak the logic of each calculation when it is used in runtime without editing the actual formula. Some calculations are parameterized by default - since their operation requires user input.
• Make Shareable: This tells the wizard to save the calculations as independent sharable logic items into the content manager (see below for more). Use this when you plan to reuse the logic in multiple reports on the platform.
  • Each calculation can be made sharable manually if this step was skipped.

Calculation Function Settings and Selection

This panel is where you elect which parts of the hierarchy will be used in driving calculations. You also need to give the wizard hints as to the type of logic to create.

• Level: Depending on your choice of dimension and hierarchy, there may be one or more items for each level (each in a separate row). In a flat hierarchy, there is only 1 level shown (see image above). In a regular hierarchy, there are usually multiple levels (see image below). Check a level item to select all available calculations for that level.
• Level Type: Critically, you must select the date-time type for that level to generate the correct calculation options. The wizard will attempt to heuristically resolve the level type for you automatically. You can use the drop downs to overwrite the automated choice and select the date-time type that matches the selected Level. (For example, if the Level is Year, then the Level Type must be Year. And if the Level is Week, then the Level Type must be Week).
• Functions: Once you have resolved the levels and their types, the wizard exposes the functions that are available (yellow box below). Here you can select which functions you wish to generate. Selecting the header checks will allow you to select all the relevant functions in a single click. The functions are described in the next section.

Note: When the Level is Date, the Level Type may be Date or Day.

Date-Time Functions in the Wizard

The Time Calculation Wizard displays a number of functions that can be used to create the required date/ time calculations based on the exact date-time type for that hierarchy or level (as explained above). The functions are categorized as follows:

• To Date: Creates calculations that returns a list of elements from the start of the given time period, up to and including the given date. The wizard produces both a list and an aggregated member. The To Date functions include: Week to Date, Month to Date, Quarter to Date, Year to Date
  • The types of To Date functions depends on the date-time type: 'MTD' is irrelevant on a year attribute.
  • When working with MS OLAP, Tabular or SAP BW models, the To Date functions are only supported when a regular or drill path hierarchy is selected; they won't appear if a flat attribute hierarchy is selected.
• Last: Create calculations that return a list of the last n given time periods. The wizard produces both a list and an aggregated member.
• Periods: Create calculations that return comparisons of different time periods.
  •  These include Period on Period and Parallel Period (which is available only when working with MS OLAP, Tabular, and BW models).
  •  If the selected date hierarchy is based on a SQL date-time column, the Period functions will also include full week, full month, full quarter, full year and Parallel Day functions.
• Current: returns the current period of the given date-time type. For example getting 'today' via the current method on the date type. Use the text box to specify the date-time format when using member captions that have unusual date-time string formats.

Time Calculation Maintenance

The elements generated are added to the target hierarchy's Custom Elements panel. As mentioned the generated elements can include members, lists, and parameters.

If the 'sharable' option is selected, the elements are also saved as independent elements into the content manager. This allows you to manage and secure them independently of the report and query.

From the elements tree, using the right click menu you can perform the following operations:

• Delete the item
• Properties: Rename the item and set its description. For custom member calculations you can change its format string and solve order
• Make it shareable (if it isn't already)
• Open the element in Formulate tools for more advanced and complete editing of the logic in the item

Further, you can embed the items into other calculations and logic:

• Use the element to create a member formula
• Create a list
• Build another parameter

• Click here for more details are managing calculations.